package com.ylh

import org.apache.spark.sql.SparkSession

object Test01 {
  def main(args: Array[String]): Unit = {
    //2)创建sparksession环境，设置master模式为本地（5分）
    val spark = SparkSession
      .builder()
      .appName("Spark SQL basic example")
      .config("spark.some.config.option", "some-value")
      .master("local[*]")
      .getOrCreate()



    //3)设置日志级别为warn，读取所给文件正确分割数据（5分）
    spark.sparkContext.setLogLevel("warn")
    //4)按照所给字段进行设置，创建成临时表，打印结构信息以及所有数据，确保数据无误，字段正确（5分）
    val student = spark.read.option("delimiter", ",").schema("stu_id string,stu_name string,birthday string,sex string,bj string,ds string").csv("data/student_info.txt")
    val teacher = spark.read.option("delimiter", ",").schema("tid string,tname string").csv("data/teacher_info.txt")
    val course = spark.read.option("delimiter", ",").schema("cid string,cname string,tid string").csv("data/course_info.txt")
    val score = spark.read.option("delimiter", ",").schema("stu_id string,cid string,score string").csv("data/score_info.txt")

    student.createOrReplaceTempView("student")
    teacher.createOrReplaceTempView("teacher")
    course.createOrReplaceTempView("course")
    score.createOrReplaceTempView("score")

    //5)查询出三门科目的平均成绩，并将其代课老师打印到控制台（5分）
    spark.sql(
      """
        |
        |select cname,tname,avg(score) avg1 from score s
        |left join course c on s.cid=c.cid
        |left join teacher t on c.tid=t.tid group by tname,cname
        |
        |""".stripMargin).show(100)

    //6)查询所有学生信息，将性别进行转换：男->man 女->women（5分）
    spark.udf.register("oneArgFilter", (sex: String) => {
      if (sex == '男') {
        "man"
      } else {
        "women"
      }
    })

    spark.sql(
      """
        |
        |select stu_id,stu_name,birthday,oneArgFilter(sex) sex1,bj,ds from student
        |""".stripMargin).show()

    //7)查询出有任意两门成绩低于60的学生信息（5分）
    spark.sql(
      """
        |select * from (
        |   select * from student
        |)t2
        |,
        |(
        |   select * from (
        |       select stu.stu_id,count(stu.stu_id) count1  from student stu
        |       left join score s on stu.stu_id=s.stu_id where score<60 group by stu.stu_id
        |   ) t1 where count1>2
        |)t3 where t2.stu_id=t3.stu_id
        |""".stripMargin).show()
    //8)查询出语文成绩前3的学生信息及分数（5分）
    spark.sql(
      """
        |
        |select * from(
        |   select * from student stu
        |   left join score s on stu.stu_id=s.stu_id
        |)t1 where cid='01' order by score desc limit 3
        |""".stripMargin).show(100)
    //9)查询出男生三科总分的平均分以及女生三科总分的平均分（5分）
    spark.sql(
      """
        |
        |   select sex,avg(score) from student stu
        |   left join score s on stu.stu_id=s.stu_id  group by sex
        |
        |""".stripMargin).show()
    //10)查询出哪位同学学过的科目数小于3（5分）
    spark.sql(
      """
        |select * from
        |(select * from student) t1,
        |(
        |   select * from (
        |       select stu.stu_id,count(stu.stu_id) count1 from student stu
        |       left join score s on stu.stu_id=s.stu_id group by stu.stu_id
        |   ) t1 where count1<3
        |)t2 where t1.stu_id=t2.stu_id
        |""".stripMargin).show()
    //11）查询和编号02教师同姓的同学信息以及总分（5分）
    spark.sql(
      """
        |
        |select stu_name,sum(score) sum1 from(
        |   select * from (
        |       select * from student stu
        |       left join score s on stu.stu_id=s.stu_id
        |   )t1,
        |   (
        |      select substr(tname,0,1) tname1 from teacher where tid='1003'
        |   )t2 where substr(t1.stu_name,0,1)=t2.tname1
        |) t3 group by stu_name
        |
        |""".stripMargin).show()
    //12）查询编号03的教师所交科目中有哪些学生本科成绩小于60分（5分）
    spark.sql(
      """
        |
        |select * from student stu
        |left join score s on stu.stu_id=s.stu_id
        |left join course c on s.cid=c.cid
        |left join teacher t on c.tid=t.tid
        |where t.tid='1003' and score <60
        |
        |
        |""".stripMargin).show()

  }
}
